package com.apple.albatross.common;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.net.URL;
import java.net.URLConnection;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Date;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;
import org.json.simple.parser.ParseException;

public class Test {

	private Connection connect = null;
	PreparedStatement preparedStatement = null;
	static final String DB_URL = "jdbc:mysql://localhost/Albatross";
	// Database credentials
	static final String USER = "root";
	static final String PASS = "";

	public static void main(String[] args) {
		String a = new Test()
				.callURL("http://isdb-api-test.corp.apple.com/api/iterator/vulnerability?show=100");

		try {
			new Test().save(a);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public String callURL(String myURL) {
		System.out.println("Requeted URL:" + myURL);
		StringBuilder sb = new StringBuilder();
		URLConnection urlConn = null;
		InputStreamReader in = null;
		try {
			URL url = new URL(myURL);
			urlConn = url.openConnection();
			if (urlConn != null)
				urlConn.setReadTimeout(60 * 10000);
			if (urlConn != null && urlConn.getInputStream() != null) {
				in = new InputStreamReader(urlConn.getInputStream(),
						Charset.defaultCharset());
				BufferedReader bufferedReader = new BufferedReader(in);
				if (bufferedReader != null) {
					int cp;
					while ((cp = bufferedReader.read()) != -1) {
						sb.append((char) cp);
					}
					bufferedReader.close();
				}
			}
			in.close();

		} catch (Exception e) {
			throw new RuntimeException("Exception while calling URL:" + myURL,
					e);
		}

		System.out.println("\nOutput: \n" + sb.toString());

		return sb.toString();

	}

	public void save(String sb) throws Exception {
		int status = 0;
		try {
			// Load the MySQL driver, each DB has its own driver
			Class.forName("com.mysql.jdbc.Driver");

			// DB connection setup
			System.out.println("Connecting to database...");
			connect = DriverManager.getConnection(DB_URL, USER, PASS);

			if (connect != null) {
				System.out.println("connected successfully");
			}
			// PreparedStatements
			preparedStatement = connect
					.prepareStatement("insert into  TESTDATA(App_id,App_ip,Host_Port,"
							+ "Last_Fixed_Datetime,Last_Found_Datetime,"
							+ "Last_Service_Modification_Datetime,Last_Test_Datetime,"
							+ "Last_Update_Datetime,Patchable,PCI_Flag,Protocol,Published_Datetime,Qualis_ID) "
							+ "values (?,?,?,?,?,?,?,?,?,?,?,?,?)");

			JSONParser parser = new JSONParser();
			String id = null;
			String ip = null, protocol = null;
			Long Host_Port;
			int patchable, pci_flag, qid;
			Date last_Fixed_Datetime, last_Found_Datetime, last_service_modification_datetime, last_test_datetime = null, last_update_datetime = null, published_datetime = null;

			try {
				Object obj = parser.parse(sb.toString());
				JSONObject mainArray = (JSONObject) obj;
				JSONArray jsonMainArr = (JSONArray) mainArray.get("results");

				System.out.println("total size" + jsonMainArr.size());
				for (int i = 0; i < jsonMainArr.size(); i++) { // **line 2**

					JSONObject childJSONObject = (JSONObject) jsonMainArr
							.get(i);
					id = (String) childJSONObject.get("id");
					ip = (String) childJSONObject.get("ip");
					Host_Port = (Long) childJSONObject.get("port");
					last_Fixed_Datetime = (Date) childJSONObject
							.get("last_fixed_datetime");
					last_Found_Datetime = (Date) childJSONObject
							.get("last_found_datetime");
					last_service_modification_datetime = (Date) childJSONObject
							.get("last_service_modification_datetime");
					last_test_datetime = (Date) childJSONObject
							.get("last_test_datetime");
					last_update_datetime = (Date) childJSONObject
							.get("last_update_datetime");
					patchable = (Integer) childJSONObject.get("patchable");
					pci_flag = (Integer) childJSONObject.get("pci_flag");
					protocol = (String) childJSONObject.get("protocol");

					published_datetime = (Date) childJSONObject
							.get("published_datetime");
					qid = (Integer) childJSONObject.get("qid");
					// Parameters start with 1
					preparedStatement.setString(1, id);
					preparedStatement.setString(2, ip);
					preparedStatement.setLong(3, Host_Port);
					preparedStatement.setDate(4,
							(java.sql.Date) last_Fixed_Datetime);
					preparedStatement.setDate(5,
							(java.sql.Date) last_Found_Datetime);
					preparedStatement.setDate(6,
							(java.sql.Date) last_service_modification_datetime);
					preparedStatement.setDate(7,
							(java.sql.Date) last_test_datetime);
					preparedStatement.setDate(8,
							(java.sql.Date) last_update_datetime);
					preparedStatement.setInt(9, patchable);
					preparedStatement.setInt(10, pci_flag);
					preparedStatement.setString(11, protocol);
					preparedStatement.setDate(12,
							(java.sql.Date) published_datetime);
					preparedStatement.setInt(13, qid);
					status = preparedStatement.executeUpdate();
					System.out.println("inserted successfully");
				}

			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		} catch (Exception e) {
			throw e;
		} finally {
			try {
				if (connect != null) {
					connect.close();
				}

			} catch (Exception e) {

			}
		}
	}
}
